In this article you will learn, everything about using Function Execution in SQL Server 2005String FunctionsDate and Time FunctionsMathematical Functions
String Functions
String Functions are used for manipulating string expression.
Note: string expression should be passed within single quote.
Len('') - Returns length of string.
Example: select Len("Shri Ganesh") will return 11
Lower('') - Convert all characters to lowercase characters.
Example: select Lower('Shri Ganesh') will return shri ganesh
Upper('') - Convert all characters to uppercase characters.
Example: select Upper('Shri Ganesh') will return SHRI GANESH
LTrim('') - Removes spaces from given character strings on left.
Example: select LTrim(' Shri Ganesh') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.
RTrim('') - Removes space from given character strings on right.
Example: select LTrim('Shri Ganesh ') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.
Trim('') - Removes spaces from given character strings from both left and right.
Example: select LTrim(' Shri Ganesh ') will return Shri Ganesh
Note: It doesn't removes tab or line feed character.
SubString('') - Returns a part of string from original string.
SubString(character_expression, position, length)position - specifies where the substring begins.length - specifies the length of the substring as number of characters.
Example: select SubString('Shri Ganesh',6,7)
where in6 - Starting position of sub string from given string.6 - It is no. of characters to be extract from given string, starting from 6.That is it will return "Ganesh" As ganesh start from 6th character upto 6 characters.
Replace('') - Replace the desired string within the original string.
Replace(character_expression, searchstring, replacementstring)SearchString - string which you want to replace.ReplaceString - new string which you want to replace with
Example: select replace('Think High To Achieve High','High','Low')here, function search for every occurrence of High and replace it with Low.
Original - Think High To Achieve High
Result - Think Low To Achieve Low
Right('') - extract particular characters from right part of given string expression.
Example: select right('Think High To Achieve High',15) will return "To Achieve High"This function will be helpful when you want particular characters from right part.
Example: Let say i have social security nos. and i want to extract last 4 digit of it
.select right('111-11-1111',4) will return 1111
select right('222-22-2222',4) will return 2222
select right('333-33-3333',4) will return 3333
select right('444-44-4444',4) will return 4444
Date and Time Function
Date and Time Functions are used for manipulating Date and Time expression.
GetDate() - Returns current date and time of a system.
Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
GetUTCDate() - Returns current date and time information as per UTC (Universal Time Coordinate or Greenwich Mean Time)
Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
DatePart and Abbrevation, which we will be using with DatePart, DateADD, DateDIFF function.
Datepart Abbreviations
Year yy, yyyy
Quarter qq, q
Month mm, m
Dayofyear dy, y
Day dd, d
Week wk, ww
Weekday dw, w
Hour Hh
Minute mi, n
Second ss, s
Millisecond Ms
DatePart() - Returns an integer representing a datepart of a date.
Note: Example are based on considering "2007-10-10 15:34:37.287" as GetDate()
Example:
select DatePart("day",GetDate()) will return 10.
select DatePart("hour",GetDate()) will return 16.
select DatePart("dayofyear",GetDate()) will return 283. And so on...
DateADD() - Returns adds a date or time interval to a specified date.
Syntax: DateADD(Abbrevation, number to be added, date)
Example:
select DateAdd("day",7,GetDate()) will return 2007-10-17 16:09:18.280
Select DateAdd("month",20,GetDate()) will return 2009-06-10 16:10:02.643
And so on...
DateDIFF() - Returns difference between two specified dates.
Syntax: DateDIFF(Abbrevation, startdate, enddate)
Note: If the end date is earlier than the start date, the function returns a negative number. If the start and end dates are equal or fall within the same interval, the function returns zero
Example:
select DATEDIFF("mm", Getdate()-500,GETDATE()) will return 17
You must pass valid start and end date otherwise you will receive error.
Mathematical Functions
Mathematical Functions are used for manipulating Mathematical expression.
ABS() - Returns positive value of numeric expression.
Example: In following example both statement will return 3.14select
ABS(3.14)
select ABS(-3.14)
Ceiling() - Returns the smallest integer that is greater than or equal to a numeric expression.
Example:
select Ceiling(3.14) will return 4
select Ceiling(-3.14) will return 3.
Floor() -Returns the largest integer that is less than or equal to a numeric expression.
Example:
select Floor(3.14) will return 3
select Floor(-3.14) will return 4
Round() - Returns a numeric expression that is rounded to the specified length or precision.
Example:
select Round(3.14, 1) will return 3.10
select Round(-3.17, 1) will return -3.20
select Round(3.12345, 4) will return 3.12350
select Round(3.12345, 3) will return 3.12300
Power() - POWER(numeric_expression, power)
Example:
select power(2,3) will return 8
